import pymysql
import json

# 建立数据库连接
conn = pymysql.connect(
    host='100.84.227.17',		# 主机名（或IP地址）
    port=3306,				# 端口号，默认为3306
    user='sx_item1',			# 用户名
    password='tPssFYeXzazNcEAc',	# 密码
    charset='utf8mb4',  		# 设置字符编码
    database='sx_item1'			# 数据库名
)

cursor = conn.cursor()

data = []

# 读取文件
with open('data.json', 'r', encoding='utf8') as f:
    data = json.load(f)

# 插入数据

count = 0
for i in range(len(data)):
    cursor.execute('INSERT INTO poem_list(poem_name, poem_dynasty, poem_author) VALUES (%s, %s, %s)', (data[i]['title'], data[i]['dynasty'], data[i]['author']))
    conn.commit()
    print('插入成功')
    # 获取插入后的主键
    cursor.execute('SELECT id FROM poem_list WHERE poem_name=%s', (data[i]['title'],))
    poem_id = cursor.fetchone()[0]

    for item in data[i]['content']:
        topic = item.split('，')
        match len(topic):
            case 1:
                cursor.execute('INSERT INTO poem_topic(poem_id, topic_1) VALUES (%s, %s)', (poem_id, topic[0]))
                count += 1
            case 2:
                cursor.execute('INSERT INTO poem_topic(poem_id, topic_1, topic_2) VALUES (%s, %s, %s)', (poem_id, topic[0], topic[1]))
                count += 1
            case 3:
                cursor.execute('INSERT INTO poem_topic(poem_id, topic_1, topic_2, topic_3) VALUES (%s, %s, %s, %s)', (poem_id, topic[0], topic[1], topic[2]))
                count += 1
            case 4:
                cursor.execute('INSERT INTO poem_topic(poem_id, topic_1, topic_2, topic_3, topic_4) VALUES (%s, %s, %s, %s, %s)', (poem_id, topic[0], topic[1], topic[2], topic[3]))
                count += 1
            case 5:
                cursor.execute('INSERT INTO poem_topic(poem_id, topic_1, topic_2, topic_3, topic_4, topic_5) VALUES (%s, %s, %s, %s, %s, %s)', (poem_id, topic[0], topic[1], topic[2], topic[3], topic[4]))
                count += 1
            case _:
                print('数据有误')
                break




    